﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Reflection;

namespace Net6Template.Tools
{
    /// <summary>
    /// 和 model displayName 一起使用
    /// 导入时只需要核对 列头与displayName值是否一致
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 第一步
        /// 上传Excel 并返回上传路径  
        /// </summary>
        /// <param name="files"></param>
        /// <param name="directoryName">文件夹名称</param>
        /// <returns>
        /// 返回路径
        /// </returns>
        private static string DepositExcel(IFormFile file, string directoryName)
        {
            //创建需要存放的位置 返回一个准确的路径
            var path = CreateDirectory("Upload/Excels/" + directoryName);
            //文件名
            string fileName = DateTime.Now.Ticks.ToString() + "." + file.FileName.Split('.').Last();
            path = path + "/" + fileName;
            if (File.Exists(path))
            {
                File.Delete(path);
            }

            using (var stream = new FileStream(path, FileMode.CreateNew))
            {
                file.CopyTo(stream);
            }
            return path;
        }

        /// <summary>
        /// 第二步
        /// 得到Excel 内容
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static (IWorkbook, ISheet) GetSheet(string path)
        {
            IWorkbook workbook;
            ISheet sheet;
            using (var file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                MemoryStream ms = new MemoryStream();
                if (Path.GetExtension(path) == ".xls")
                {
                    workbook = new HSSFWorkbook(file);
                    //获取一个sheetName
                    sheet = workbook.GetSheetAt(0);
                }
                else
                {
                    workbook = new XSSFWorkbook(file);
                    //获取一个sheetName
                    sheet = workbook.GetSheetAt(0);
                }
            }
            return (workbook, sheet);
        }

        /// <summary>
        /// 第三步
        /// 根据Excel 内容得到想要的List
        /// </summary>
        /// <typeparam name="T"></typeparam> 
        /// <param name="sheet"></param>
        /// <param name="headNum"></param>
        /// <returns></returns>
        private static List<T> GetList<T>(ISheet sheet, int headNum)
        {
            List<T> list = new List<T>();
            Dictionary<int, string> dict = new Dictionary<int, string>();
            //获得列名所对应的字段名
            var propertys = GetPropertyByType<T>(false);
            //得到每个字段对应的序号
            IRow head = sheet.GetRow(headNum);

            for (int i = 0; i < head.LastCellNum; i++)
            {
                ICell cell = head.GetCell(i);
                if (propertys.ContainsKey(cell.StringCellValue.Trim()))
                {
                    dict.Add(i, propertys[cell.StringCellValue.Trim()]);
                }
            }
            if (dict.Count != head.LastCellNum)
            {
                throw new Exception("Import tables head and requirements inconsistency");
            }
            var type = typeof(T).GetProperties();
            int c = 0;
            try
            {
                for (int i = headNum + 1; i <= sheet.LastRowNum; i++)
                {
                    c = i;
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        bool isAddList = true;
                        T t = Activator.CreateInstance<T>();
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            ICell cell = row.GetCell(j);
                            string name = "";
                            dict.TryGetValue(j, out name);
                            if (cell != null)
                            {
                                if (cell.CellType == CellType.Blank)//空值
                                {
                                    isAddList = IsAdd(name, true);
                                }
                                else
                                {
                                    var item = type.FirstOrDefault(m => m.Name == name);
                                    if (item != null)
                                    {
                                        if (item.PropertyType == typeof(DateTime))
                                        {
                                            try
                                            {
                                                if (cell.CellType == CellType.String)
                                                {
                                                    var value = Convert.ToDateTime(cell.ToString());
                                                    item.SetValue(t, value);
                                                }
                                                else
                                                {
                                                    item.SetValue(t, cell.DateCellValue);
                                                }
                                            }
                                            catch
                                            {
                                                throw new Exception($"DateTime{cell.ToString()}格式不正确!");
                                            }
                                        }
                                        else if (item.PropertyType == typeof(int))
                                        {
                                            try
                                            {
                                                item.SetValue(t, Convert.ToInt32(cell.ToString()));
                                            }
                                            catch
                                            {
                                                throw new Exception($"int{cell.ToString()}格式不正确!");
                                            }
                                        }
                                        else if (item.PropertyType == typeof(string))
                                        {
                                            if (cell.CellType == CellType.String)
                                            {
                                                item.SetValue(t, cell.ToString());
                                                isAddList = IsAdd(name, string.IsNullOrEmpty(cell.ToString()));
                                            }
                                            else
                                            {
                                                item.SetValue(t, cell.NumericCellValue.ToString());
                                            }

                                        }
                                        else if (item.PropertyType == typeof(decimal?) || item.PropertyType == typeof(decimal))
                                        {
                                            if (cell != null)
                                            {
                                                try
                                                {
                                                    var value = 0m;
                                                    if (cell.CellType == CellType.String)
                                                    {
                                                        value = Convert.ToDecimal(cell.ToString());
                                                    }
                                                    else
                                                    {
                                                        value = Convert.ToDecimal(cell.NumericCellValue);
                                                    }
                                                    item.SetValue(t, value);
                                                    isAddList = IsAdd(name, value == 0);
                                                }
                                                catch
                                                {
                                                    throw new Exception($"decimal{cell.ToString()}格式不正确!");
                                                }
                                            }
                                        }
                                    }
                                }
                                if (isAddList == false)
                                {
                                    break;
                                }
                            }
                        }
                        if (isAddList)
                        {
                            list.Add(t);
                        }
                    }

                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return list;
        }

        private static bool IsAdd(string name, bool isOk)
        {
            bool result = true;
            string[] isNotStrs = { };
            if (isNotStrs.Contains(name) && isOk)
            {
                result = false;
            }
            return result;
        }

        #region 辅助方法
        /// <summary>
        /// 创建目录
        /// </summary>
        /// <param name="directoryPath">目录路径</param>
        private static string CreateDirectory(string directoryPath = "")
        {
            var path = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content");
            if (!string.IsNullOrEmpty(directoryPath))
            {
                if (directoryPath.Substring(0, 1) != "/")
                {
                    directoryPath = "/" + directoryPath;
                }
                path += directoryPath;
            }
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            return path;
        }

        /// <summary>
        /// 获得Excel列名
        /// </summary>
        private static Dictionary<string, string> GetPropertyByType<In>(bool isToExcel)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            var type = typeof(In);
            try
            {
                foreach (var item in type.GetProperties())
                {
                    var displayName = item.GetCustomAttribute<DisplayNameAttribute>();
                    if (displayName != null)
                    {
                        if (isToExcel)
                        {
                            dict.Add(item.Name, displayName.DisplayName);
                        }
                        else
                        {
                            dict.Add(displayName.DisplayName, item.Name);
                        }
                    }

                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return dict;
        }
        #endregion

        /// <summary>
        ///  功能,
        ///  导入Excel
        ///  列头名和实体的DispName 要一致。
        /// </summary>
        /// <typeparam name="T">要转换的实体</typeparam>
        /// <param name="files">上传的Excel文件</param>
        /// <param name="headNum">Excel头部行数</param>
        /// <returns>
        /// 获得转换后的List 集合
        /// </returns>
        public static List<T> GetList<T>(IFormFile files, int headNum)
        {
            List<T> list = new List<T>();
            string path = DepositExcel(files, typeof(T).Name);
            //得到上传文件内容
            var (workbook, sheet) = GetSheet(path);
            //转换成List
            var t = GetList<T>(sheet, headNum);
            if (t != null && t.Count > 0)
            {
                list.AddRange(t);
            }
            return list;
        }



        /// <summary>
        ///  功能,
        ///  导入Excel
        /// </summary>
        /// <typeparam name="T">要转换的实体</typeparam>
        /// <param name="files">上传的Excel文件</param>
        /// <param name="headNum">Excel头部行数</param>
        /// <returns>
        /// 获得转换后的List 集合
        /// </returns>
        public static DataTable GetDataTable(IFormFile files, int headNum)
        {
            string path = DepositExcel(files, "temp");
            //得到上传文件内容
            var (workbook, sheet) = GetSheet(path);
            return ExcelToDataTable(workbook, sheet);
        }

        /// <summary>
        /// 生成Excel流数据，
        /// return File(memoryStream.ToArray(), "application/vnd.ms-excel", fileName); //vnd.ms 此模式有些不兼容
        /// 或者
        /// return File(memoryStream.ToArray(), "application/ms-excel", "红包列表.xls")
        /// </summary>
        /// <typeparam name="T">数据模型</typeparam>
        /// <param name="excelType">excel扩展名类型</typeparam>
        /// <param name="data">数据集</param>
        /// <param name="sheetSize">Excel的单个Sheet的行数，不能超过65535，否则会抛出异常</param>
        /// <returns></returns>
        public static MemoryStream ToExcel<T>(List<T> data, string excelType = "xlsx", int sheetSize = 50000)
        {
            IWorkbook wk;
            if (excelType == "xlsx")
            {
                wk = new XSSFWorkbook();
            }
            else
            {
                wk = new HSSFWorkbook();
            }
            var itemType = Activator.CreateInstance<T>().GetType();

            int baseNum = 65535;//单个Sheet最大行数65535
            int cNum = data.Count / baseNum;
            int myForCount = data.Count % baseNum == 0 ? cNum : cNum + 1;
            for (int i = 0; i < myForCount; i++)
            {
                var list = data.Skip(i * baseNum).Take(baseNum).ToList();
                string sheetName = "sheet" + i;
                CreateSheet(wk, list, itemType, sheetName);
            }
            MemoryStream m = new MemoryStream();
            wk.Write(m);
            return m;
        }

        /// <summary>
        /// 创建并得到一个 sheet
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="IN"></typeparam>
        /// <param name="wk"></param>
        /// <param name="data"></param>
        /// <param name="itemType"></param>
        /// <param name="sheetName"></param>
        /// <param name="sheetSize"></param>
        /// <param name="valueHandlerDict"></param>
        private static void CreateSheet<T>(IWorkbook wk, List<T> data, Type itemType, string sheetName, int sheetSize = 50000)
        {
            try
            {
                ISheet sheet = null;
                var headers = GetPropertyByType<T>(true);
                sheet = CreateHeaders(wk, headers, sheetName);
                if (data.Count > 0)
                {
                    for (var i = 0; i < data.Count; i++)
                    {
                        //创建内容
                        IRow row = sheet.CreateRow(i % sheetSize + 1);

                        //遍历填充每条数据
                        int j = 0;
                        foreach (var item in headers)
                        {
                            var p = itemType.GetProperty(item.Key);//获取对应列名
                            if (p != null)
                            {
                                var value = p.GetValue(data[i]);
                                value = value == null ? string.Empty : value;
                                ICell cell = row.CreateCell(j);
                                cell.SetCellValue(value.ToString());
                            }
                            j++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 创建sheet 表头
        /// </summary>
        /// <param name="wk">workbook</param>
        /// <param name="headers">表头</param>
        /// <param name="sheetName"></param>
        /// <returns>
        /// 返回一个sheet
        /// </returns>
        private static ISheet CreateHeaders(IWorkbook wk, Dictionary<string, string> headers, string sheetName)
        {
            var sheet = wk.CreateSheet(sheetName);
            IRow rowHead = sheet.CreateRow(0);
            ICellStyle style = wk.CreateCellStyle();
            IFont font = wk.CreateFont();//创建字体样式
            font.IsBold = true;
            style.SetFont(font);

            int i = 0;
            foreach (var item in headers)
            {
                ICell cellHead = rowHead.CreateCell(i);
                cellHead.SetCellValue(item.Value);
                cellHead.CellStyle = style;
                i++;
            }
            return sheet;
        }

        /// <summary>
        /// 读取excel表格中的数据,将Excel文件流转化为dataTable数据源  
        /// 默认第一行为标题 
        /// </summary>
        /// <param name="stream">excel文档文件流</param>
        /// <param name="fileType">文档格式</param>
        /// <param name="isSuccess">是否转化成功</param>
        /// <param name="resultMsg">转换结果消息</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(IWorkbook workbook, ISheet sheet)
        {
            var excelToDataTable = new DataTable();


            //Workbook对象代表一个工作簿,首先定义一个Excel工作薄
            // IWorkbook workbook;

            /**
            //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
            #region 判断Excel版本
            switch (fileType)
            {
                //.XLSX是07版(或者07以上的)的Office Excel
                case ".xlsx":
                    workbook = new XSSFWorkbook(stream);
                    break;
                //.XLS是03版的Office Excel
                case ".xls":
                    workbook = new HSSFWorkbook(stream);
                    break;
                default:
                    throw new Exception("Excel文档格式有误");
            }
            #endregion

            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();
            
            **/
            var headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;//最后一行列数（即为总列数）

            //获取第一行标题列数据源,转换为dataTable数据源的表格标题名称
            for (var j = 0; j < cellCount; j++)
            {
                var cell = headerRow.GetCell(j);
                excelToDataTable.Columns.Add(cell.ToString());
            }

            //获取Excel表格中除标题以为的所有数据源，转化为dataTable中的表格数据源
            for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                var dataRow = excelToDataTable.NewRow();

                var row = sheet.GetRow(i);

                if (row == null) continue; //没有数据的行默认是null　

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)//单元格内容非空验证
                    {
                        #region NPOI获取Excel单元格中不同类型的数据
                        //获取指定的单元格信息
                        var cell = row.GetCell(j);
                        switch (cell.CellType)
                        {
                            //首先在NPOI中数字和日期都属于Numeric类型
                            //通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型
                            case CellType.Numeric when DateUtil.IsCellDateFormatted(cell):
                                dataRow[j] = cell.DateCellValue;
                                break;
                            case CellType.Numeric:
                                //其他数字类型
                                dataRow[j] = cell.NumericCellValue;
                                break;
                            //空数据类型
                            case CellType.Blank:
                                dataRow[j] = "";
                                break;
                            //公式类型
                            case CellType.Formula:
                                {
                                    //HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                    XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
                                    dataRow[j] = eva.Evaluate(cell).NumberValue;
                                    //dataRow[j] = "";
                                    break;
                                }
                            //布尔类型
                            case CellType.Boolean:
                                dataRow[j] = row.GetCell(j).BooleanCellValue;
                                break;
                            //错误
                            case CellType.Error:
                                dataRow[j] = "";
                                break;
                            //其他类型都按字符串类型来处理（未知类型CellType.Unknown，字符串类型CellType.String）
                            default:
                                dataRow[j] = cell.StringCellValue;
                                break;
                        }
                        #endregion
                    }
                }
                excelToDataTable.Rows.Add(dataRow);
            }
            return excelToDataTable;
        }

        public static byte[] GetExcelPackageData(List<string[]> datas)
        {
#if DEBUG
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
#endif
            using var p = new ExcelPackage();
            // 用户
            if (datas != null)
            {
                var sheet = p.Workbook.Worksheets.Add("导入失败用户列表");

                // 标题
                var cellIndex = 1;
                sheet.Cells[1, cellIndex++].Value = "名称";
                sheet.Cells[1, cellIndex++].Value = "代码";
                sheet.Cells[1, cellIndex++].Value = "营业收入同比增长率";
                sheet.Cells[1, cellIndex++].Value = "营业收入环比增长率";
                sheet.Cells[1, cellIndex++].Value = "营业收入环比增长加速度";
                sheet.Cells[1, cellIndex++].Value = "PE(市盈率)";
                sheet.Cells[1, cellIndex++].Value = "PS(市销率)";
                sheet.Cells[1, cellIndex++].Value = "PSG";
                sheet.Cells[1, cellIndex++].Value = "月活用户数环比增长率";
                sheet.Cells[1, cellIndex++].Value = "人均花费";
                sheet.Cells[1, cellIndex].Value = "获客成本";
                // 背景色
                sheet.Cells[1, 1, 1, cellIndex].Style.Fill.PatternType = ExcelFillStyle.LightGrid;
                sheet.Cells[1, 1, 1, cellIndex].Style.Fill.BackgroundColor.SetColor(Color.Yellow);

                var row = 2;
                foreach (var item in datas)
                {
                    cellIndex = 1;
                    sheet.Cells[row, cellIndex++].Value = item[0];
                    sheet.Cells[row, cellIndex++].Value = item[1];
                    sheet.Cells[row, cellIndex++].Value = item[2];
                    sheet.Cells[row, cellIndex++].Value = item[3];
                    sheet.Cells[row, cellIndex++].Value = item[4];
                    sheet.Cells[row, cellIndex++].Value = item[5];
                    sheet.Cells[row, cellIndex++].Value = item[6];
                    sheet.Cells[row, cellIndex++].Value = item[7];
                    sheet.Cells[row, cellIndex++].Value = item[8];
                    sheet.Cells[row, cellIndex++].Value = item[9];
                    sheet.Cells[row, cellIndex].Value = item[10];
                    row++;
                }
            }
            return p.GetAsByteArray();
        }

    }
}
